#!/usr/bin/env node

/**
 * 数据持久化验证脚本
 * 验证数据导入后的存储一致性和前端显示
 */

import { getDeviceParameterDb } from '../lib/device-parameter-database';

async function verifyDataPersistence() {
  console.log('🔍 开始验证数据持久化...\n');

  try {
    const db = getDeviceParameterDb();
    
    // 1. 检查devices表结构和数据
    console.log('1. 验证devices表结构...');
    const devices = db.getAllDevices();
    console.log(`   设备总数: ${devices.length}`);
    
    if (devices.length > 0) {
      const device = devices[0];
      console.log('   设备表字段结构:');
      Object.keys(device).forEach(key => {
        console.log(`      ${key}: ${typeof device[key]} = ${device[key]}`);
      });
    }
    console.log();

    // 2. 检查关联记录ID的填充情况
    console.log('2. 验证设备关联记录ID...');
    const devicesWithRecords = devices.filter(d => 
      d.naming_record_id || d.position_record_id || d.base_params_record_id
    );
    
    console.log(`   有关联记录的设备: ${devicesWithRecords.length}/${devices.length}`);
    console.log(`   关联记录填充率: ${((devicesWithRecords.length / devices.length) * 100).toFixed(1)}%`);
    
    if (devicesWithRecords.length > 0) {
      const deviceWithRecords = devicesWithRecords[0];
      console.log('   关联记录示例:');
      console.log(`      naming_record_id: ${deviceWithRecords.naming_record_id}`);
      console.log(`      position_record_id: ${deviceWithRecords.position_record_id}`);
      console.log(`      base_params_record_id: ${deviceWithRecords.base_params_record_id}`);
    }
    console.log();

    // 3. 检查参数表数据
    console.log('3. 验证参数表数据...');
    const mappings = db.getAllDeviceTypeMappings();
    let totalParamRecords = 0;
    let tablesWithData = 0;
    
    for (const mapping of mappings.slice(0, 5)) { // 检查前5个映射
      try {
        const namingFields = db.getParameterTableFields(mapping.naming_table);
        const positionFields = db.getParameterTableFields(mapping.position_table);
        const baseFields = db.getParameterTableFields(mapping.base_params_table);
        
        const totalFields = namingFields.length + positionFields.length + baseFields.length;
        if (totalFields > 0) {
          tablesWithData++;
          totalParamRecords += totalFields;
        }
        
        console.log(`   ${mapping.classification_name}:`);
        console.log(`      命名表字段: ${namingFields.length}`);
        console.log(`      位置表字段: ${positionFields.length}`);
        console.log(`      基础参数表字段: ${baseFields.length}`);
      } catch (error) {
        console.log(`   ❌ ${mapping.classification_name}: 表结构错误`);
      }
    }
    
    console.log(`   有数据的参数表: ${tablesWithData}/5`);
    console.log(`   参数字段总数: ${totalParamRecords}`);
    console.log();

    // 4. 检查专业分布
    console.log('4. 验证专业分布...');
    const specialtyStats = devices.reduce((acc, device) => {
      acc[device.specialty] = (acc[device.specialty] || 0) + 1;
      return acc;
    }, {} as Record<string, number>);
    
    Object.entries(specialtyStats).forEach(([specialty, count]) => {
      console.log(`   ${specialty}: ${count} 台设备`);
    });
    console.log();

    // 5. 检查设备ID格式
    console.log('5. 验证设备ID格式...');
    const deviceIdPatterns = devices.reduce((acc, device) => {
      const prefix = device.device_id.split('-')[0];
      acc[prefix] = (acc[prefix] || 0) + 1;
      return acc;
    }, {} as Record<string, number>);
    
    Object.entries(deviceIdPatterns).forEach(([prefix, count]) => {
      console.log(`   ${prefix}-XXXX 格式: ${count} 个设备`);
    });
    console.log();

    // 6. 检查数据完整性
    console.log('6. 验证数据完整性...');
    const incompleteDevices = devices.filter(d => 
      !d.device_id || !d.name || !d.classification_code || !d.specialty
    );
    
    if (incompleteDevices.length === 0) {
      console.log('   ✅ 所有设备都有完整的基础信息');
    } else {
      console.log(`   ❌ ${incompleteDevices.length} 个设备缺少基础信息`);
    }

    const orphanedDevices = devices.filter(d => {
      const mapping = db.getDeviceTypeMapping(d.classification_code);
      return !mapping;
    });
    
    if (orphanedDevices.length === 0) {
      console.log('   ✅ 所有设备都有对应的类型映射');
    } else {
      console.log(`   ❌ ${orphanedDevices.length} 个设备没有对应的类型映射`);
    }
    console.log();

    // 7. 检查时间戳
    console.log('7. 验证时间戳...');
    const devicesWithTimestamps = devices.filter(d => d.created_at && d.updated_at);
    console.log(`   有时间戳的设备: ${devicesWithTimestamps.length}/${devices.length}`);
    
    if (devicesWithTimestamps.length > 0) {
      const recentDevice = devicesWithTimestamps.sort((a, b) => 
        new Date(b.created_at!).getTime() - new Date(a.created_at!).getTime()
      )[0];
      console.log(`   最新设备创建时间: ${recentDevice.created_at}`);
    }
    console.log();

    // 8. 生成数据质量报告
    console.log('📊 数据质量报告:');
    const qualityScore = {
      基础信息完整性: incompleteDevices.length === 0 ? 100 : ((devices.length - incompleteDevices.length) / devices.length * 100),
      类型映射完整性: orphanedDevices.length === 0 ? 100 : ((devices.length - orphanedDevices.length) / devices.length * 100),
      关联记录填充率: (devicesWithRecords.length / devices.length * 100),
      时间戳完整性: (devicesWithTimestamps.length / devices.length * 100)
    };

    Object.entries(qualityScore).forEach(([metric, score]) => {
      const status = score >= 90 ? '✅' : score >= 70 ? '⚠️' : '❌';
      console.log(`   ${status} ${metric}: ${score.toFixed(1)}%`);
    });

    const averageScore = Object.values(qualityScore).reduce((sum, score) => sum + score, 0) / Object.keys(qualityScore).length;
    console.log(`\n   总体数据质量评分: ${averageScore.toFixed(1)}%`);

    if (averageScore >= 90) {
      console.log('\n🎉 数据持久化验证通过！数据质量优秀。');
    } else if (averageScore >= 70) {
      console.log('\n⚠️ 数据持久化基本正常，但有改进空间。');
    } else {
      console.log('\n❌ 数据持久化存在问题，需要进一步修复。');
    }

  } catch (error) {
    console.error('❌ 验证失败:', error);
    process.exit(1);
  }
}

// 运行验证
if (require.main === module) {
  verifyDataPersistence();
}